In [1]:
import pandas as pd
import json
import swat
import plotly.plotly as py
import plotly.graph_objs as go
import folium
from ipywidgets import *
from IPython.display import display
from IPython.display import clear_output
from IPython.html import widgets
from plotly.offline import init_notebook_mode, iplot
from pprint import pprint
from folium.plugins import MarkerCluster
from folium import CircleMarker
init_notebook_mode()
index_name = 'SFPD_INCIDENTS_2015_INDEX'
In [2]:
conn = swat.CAS('cas01', 19640)
In [3]:
dataTable = conn.upload('data/SFPD_Incidents_2015.csv')
In [4]:
if conn.tableexists(name=index_name):
conn.droptable(name=index_name)
conn.search.buildindex(index={'name':index_name, 'promote':True},
schema='[{"field":"Category","isKey":false,"isIndex":true,"fieldType":"string","isMultiValue":false},\
{"field":"Descript","isKey":false,"isIndex":true,"fieldType":"string","isMultiValue":false},\
{"field":"DayOfWeek","isKey":false,"isIndex":true,"fieldType":"string","isMultiValue":false},\
{"field":"PdDistrict","isKey":false,"isIndex":true,"fieldType":"string","isMultiValue":false},\
{"field":"Date","isKey":false,"fieldType":"datetime","isMultiValue":false},\
{"field":"Address","isKey":false,"isIndex":true,"fieldType":"string","isMultiValue":false},\
{"field":"Resolution","isKey":false,"isIndex":true,"fieldType":"string","isMultiValue":false},\
{"field":"GeoPoint","isKey":false,"fieldType":"geo","isMultiValue":false},\
{"field":"X","isKey":false,"fieldType":"float","isMultiValue":false},\
{"field":"Y","isKey":false,"fieldType":"float","isMultiValue":false}]')
Out[4]:
In [5]:
conn.search.appendindex(index={'name':index_name},
table={'name':'SFPD_INCIDENTS_2015'})
Out[5]:
In [6]:
conn.search.searchindex(casout={'name':'allResults','replace':True},
index={'name':index_name},
json='{"query":{"matchAll":true}}',
jsonout=False
)
Out[6]:
This steps displays two input text fields. The first field is to input search term. You may also do a phrase search by enclosing the whole phrase in escaped double quotation marks. For example, \"a phrase\". The radius input field can be left blank: in this case all results matching the search term in the index (regardless geo locations) will be reported. When radius is given, a circle with that radius centered at (lat: 37.79, long: -122.415) is formed and only results falling within that circle will be reported. All matching records will be saved in a CAS table 'allResults' for later steps to consume.
In [8]:
text = widgets.Text(description="Search term", width=200)
radius = widgets.Text(description="Radius (km)", width=200)
display(text)
display(radius)
def handle_submit(sender):
if radius.value=='':
results = conn.search.searchindex(
index={'name':index_name},
json='{"query":{"simplequery":{"query":"' + text.value + '"}},"output":{"fields":["Descript",\
"Category","PdDistrict","DayOfWeek","Resolution","Address","X","Y","Date"]}}',
jsonout=False,
casout={'name':'allResults','replace':True})
else:
results = conn.search.searchindex(
index={'name':index_name},json='{"query":{"andquery":[{"simplequery":{"query":"' \
+ text.value + '"}},{"geofilter":{"field":"GeoPoint","shape":{"coordinates":[-122.415,37.79],\
"type":"circle","radius":'+radius.value+'}}}]}, "output":{"fields":["Descript","Category","PdDistrict",\
"DayOfWeek","Resolution","Address","X","Y","Date"]}}',
jsonout=False,
casout={'name':'allResults','replace':True})
clear_output()
display(results)
text.on_submit(handle_submit)
radius.on_submit(handle_submit)
This step draws search results in 'allResults' CAS table on the google map. When Geo filter radius is given, it draws a circle on the map corresponding to the filtering shape. Clustered markers on the map can be clicked and the map will zoom in to show more details in that area.
In [9]:
coords = conn.table.fetch(fetchvars=['X', 'Y'],
table={'name':'allResults'},
maxrows=5000,
to=1000)
xs=coords.Fetch['X']
ys=coords.Fetch['Y']
SF_COORDINATES = (37.76, -122.45)
map = folium.Map(location=SF_COORDINATES, zoom_start=12)
locations = [];
for i, val in enumerate(xs):
locations.append([ys[i], val])
h = folium.FeatureGroup(name='Hydroelectric')
h.add_children(MarkerCluster(locations=locations))
map.add_children(h)
map.add_child(folium.LatLngPopup())
try:
radius
except NameError:
pass
else:
if radius.value != '':
map.add_children(CircleMarker(location=[37.79,-122.415],
radius=float(radius.value)*1000,
fill_opacity=0.05))
display(map)
In [10]:
response = conn.search.valuecount(json='{"count":5,"fields":["Category","DayOfWeek","PdDistrict","Resolution"]}',
jsonout=False,
table={'name':'allResults'})
display(response)
In [11]:
# Run datetime histogram aggregation
resp = dict(conn.search.searchaggregate(table={'name':'allResults'},
json='{"name":"daily incidents","groupby":[{"histogram":{"unit":"day","min":"2015-01-01","max":"2015-12-31",\
"interval":1},"field":"Date"}]}',
jsonout=True))
result = json.loads(resp['result'])
aggResult = dict(result['aggregation'])
groups = aggResult['group'];
x1=[]
y1=[]
for val in groups:
groupMap = dict(val)
x1.append(groupMap['name'])
y1.append(groupMap['count'])
trace1 = go.Scatter(
x=x1,
y=y1,
fill='tozeroy'
)
iplot([trace1])
In [12]:
conn.close()
In [ ]: